from common.setting import ensure_path_sep
from utils.mysqlUtils.sql_server_utils_ import MSSQL
from utils.mysqlUtils.db_pool import Database
from utils.noticeUtils.dingtalkControl import DingTalkSendMsg



class TestUpcUsage:
    """
    Walmart upc使用情况
    """
    def test_check_walmart_upc_usage(self):

        erp_db = MSSQL(ensure_path_sep("\\config\\config.yaml"), database_name="sqlserver_db_erp_base")
        zhcxkj_center_listing_comprehensive_db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"),
                                                          "zhcxkj_center_listing_comprehensive")
        erp_upc = """
            SELECT CASE 
            WHEN IsWalmartUsed=0 OR IsWalmartUsed IS null THEN '未使用'
            ELSE '刊登已使用'
            END AS '校验使用情况',
            COUNT(*) num FROM [dbo].[RB_Product_UPC] WHERE [IsWalmartUsed] = N'0' OR [IsWalmartUsed] IS NULL GROUP BY IsWalmartUsed
                        """
        erp_upc_res = erp_db.ExecQuery(erp_upc, ())
        print(erp_upc_res)
        print(erp_upc_res[0][1])

        # zhcxkj_center_listing_comprehensive_upc = f"""
        #     SELECT CASE
        #     WHEN `status`=0 THEN '未校验'
        #     WHEN `status`=1 THEN '等待结果'
        #     WHEN `status`=2 THEN '未使用'
        #     WHEN `status`=3 THEN '采集失败'
        #     WHEN `status`=4 THEN '刊登已使用'
        #     ELSE '外部已使用' END AS '校验使用情况',
        #     COUNT(*) num FROM `zhcxkj_center_listing_comprehensive`.`t_walmart_upc` GROUP BY `status`
        #                 """
        # zhcxkj_center_listing_comprehensive_upc_res = zhcxkj_center_listing_comprehensive_db.fetch_all_tuple(zhcxkj_center_listing_comprehensive_upc, ())
        # print(zhcxkj_center_listing_comprehensive_upc_res)

        if not erp_upc_res:
            text = f"Walmart upc使用情况：\n\n"\
                   f"ERP导入未使用的UPC剩余数量为：0个，请及时导入维护UPC!\n\n"
        else:
            if erp_upc_res[0][1] < 200000:
                text = f"Walmart upc使用情况：\n\n"\
                       f"ERP导入未使用的UPC剩余数量为：{erp_upc_res[0][1]}个，低于20W请及时导入维护UPC!\n\n"
            else:
                text = f"Walmart upc使用情况：\n\n"\
                       f"ERP导入未使用的UPC剩余数量为：{erp_upc_res[0][1]}个\n\n"

        DingTalkSendMsg(DingTalk='Walmart_monitor').send_success(text=text,
                                                                     title='Walmart upc使用情况(ERP库)',
                                                                     phone_list=[13538072163, 13242975788])


if __name__ == '__main__':
    TestUpcUsage().test_check_walmart_upc_usage()
